﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Service.DB
{
    public class SqlServerHelper
    {
        #region 成员
        /// <summary>
        /// The _unique instance
        /// </summary>
        private static SqlServerHelper _Instance;
        /// <summary>
        /// 获取实例
        /// </summary>
        /// <returns>DbService.</returns>
        public static SqlServerHelper Instance
        {
            get
            {
                // 当第一个线程运行到这里时，此时会对locker对象 "加锁"，
                // 当第二个线程运行该方法时，首先检测到locker对象为"加锁"状态，该线程就会挂起等待第一个线程解锁
                // lock语句运行完之后（即线程运行完之后）会对该对象"解锁"
                lock (Locker)
                {
                    // 如果类的实例不存在则创建，否则直接返回
                    if (_Instance == null)
                    {
                        _Instance = new SqlServerHelper();
                    }
                }

                return _Instance;
            }
        }
        #endregion

        #region 属性
        /// <summary>
        /// 定义一个标识确保线程同步
        /// </summary>
        private static readonly object Locker = new object();

        //<connectionStrings>
        //    <add name = "sqlConnString" connectionString="server=.\MSSQLSERVER1;database=数据库名;uid=sa;pwd=*****;Integrated Security=False;Persist Security Info=False;"       providerName="System.Data.Sqlclient"/>
        //</connectionStrings>

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private readonly string connString = ConfigurationManager.ConnectionStrings["sqlConnString"].ToString();  //数据库连接字符 
        #endregion

        #region 方法
        /// <summary>
        /// 根据id查询对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="id">对象实例的Id(泛型：类型int或string)</param>
        /// <param name="idName">条件的字段名称（主键名）</param>
        /// <returns></returns>
        public T QueryById<T, I>(I id, string idName = "Id")
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
            string sqlString = string.Format("select {0} from [{1}] where {2}={3}", columnString, 
                type.Name, idName, id.GetType().Name.ToString() == "String" ? ("'" + id.ToString() + "'") : id.ToString());
            var t = Activator.CreateInstance(type);
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlString, conn);
                SqlDataReader reader = sqlCommand.ExecuteReader();
                reader.Read();
                SetValueByProperties(type, reader, t);
            }
            return (T)t;
        }

        /// <summary>
        /// 获取数据列表
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <returns></returns>
        public List<T> QueryAll<T>()
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
            string sqlString = string.Format("select {0} from [{1}]", columnString, type.Name);
            List<T> dataList = new List<T>();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlString, conn);
                SqlDataReader reader = sqlCommand.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var t = Activator.CreateInstance(type);
                        SetValueByProperties(type, reader, t);
                        dataList.Add((T)t);
                    }
                }
                else
                {
                    return null;
                }
            }
            return dataList;
        }

        /// <summary>
        /// 插入对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="t">对象实例</param>
        /// <param name="idName">不插入的字段（自增键名）</param>
        /// <returns></returns>
        public bool Insert<T>(T t, string idName = "Id")
        {
            Type type = typeof(T);
            string sqlString = "insert [{0}] ({1}) values ({2})";
            string columnString = string.Join(",", type.GetProperties().Where(p => p.Name != idName).Select(p => string.Format("[{0}]", p.Name)));
            string valueString = string.Join(",", type.GetProperties().Where(p => p.Name != idName).Select(p => string.Format("@{0}", p.Name)));
            sqlString = string.Format(sqlString, type.Name, columnString, valueString);
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlString, conn);
                SqlParameter[] sqlParameter = type.GetProperties().Where(p => p.Name != idName).Select(p => new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(t, null) ?? DBNull.Value)).ToArray();
                sqlCommand.Parameters.AddRange(sqlParameter);
                return sqlCommand.ExecuteNonQuery() > 0;
            }
        }

        /// <summary>
        /// 修改对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="t">对象实例</param>
        /// <param name="idName">自增键名或条件名</param>
        /// <returns></returns>
        public bool Update<T>(T t, string idName = "Id")
        {
            Type type = typeof(T);
            string sqlString = "update [{0}] set {1} where {2}={3}";
            string setString = string.Join(",", type.GetProperties().Where(p => p.Name != idName).Select(p => string.Format("[{0}]=@{0}", p.Name)));
            sqlString = string.Format(sqlString, type.Name, setString, idName, "@" + idName);
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlString, conn);
                SqlParameter[] sqlParameter = type.GetProperties().Select(p => new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(t, null) ?? DBNull.Value)).ToArray();
                sqlCommand.Parameters.AddRange(sqlParameter);
                return sqlCommand.ExecuteNonQuery() > 0;
            }
        }

        /// <summary>
        /// 删除对象
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="t">对象实例</param>
        /// <param name="idName">不插入的字段（自增键名）</param>
        /// <returns></returns>
        public bool Delete<T>(T t, string idName = "Id")
        {
            Type type = typeof(T);
            string sqlString = $"delete from { type.Name}";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand sqlCommand = new SqlCommand(sqlString, conn);
                SqlParameter[] sqlParameter = type.GetProperties().Where(p => p.Name != idName).Select(p => new SqlParameter(string.Format("@{0}", p.Name), p.GetValue(t, null) ?? DBNull.Value)).ToArray();
                sqlCommand.Parameters.AddRange(sqlParameter);
                return sqlCommand.ExecuteNonQuery() > 0;
            }
        }

        /// <summary>
        /// 设置值by属性（SQLreader）
        /// </summary>
        /// <param name="type">对象类型</param>
        /// <param name="reader">sqlreader</param>
        /// <param name="t">对象</param>
        private void SetValueByProperties(Type type, SqlDataReader reader, object t)
        {
            foreach (var item in type.GetProperties())
            {
                if (reader[item.Name] is DBNull) //判空
                {
                    item.SetValue(t, null);
                }
                else
                {
                    item.SetValue(t, reader[item.Name]);
                }
            }
        }
        #endregion
    }
}